USE [UFDATA_001_2024]
GO
/****** Object:  StoredProcedure [dbo].[SaveStockIn4MO]    Script Date: 2024-05-09 14:57:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
2024-08-15修改
加入了自定义字段
*/
/*
2024-08-12修改
1.加入了库位支持，参数名是@loc，如果仓库不支持库位，则可不传。
2.加入了单据是否审核的支持，参数是@doAudit，可不传，默认审核，如不审核，则传0
*/

/*******说明*********
本存储过程可以根据单号，生产订单子件ID，创建人，仓库，数量等信息来创建一个只有一行明细的材料出库单，并进行审核
其中创建人、仓库、行ID以及数量等合法性，需要由调用者来保证正确。
真实部署时，应当修改cAcc_Id='001'为实际账套号。
本程序不支持以下要素
1.批号
2.序列号
3.自由项
4.多单位
5.出库跟踪入库
6.预留
7.倒冲
8.检查发料情况
9.自动关闭
本程序经过以下测试，但请务必在客户环境上重新进行下列测试
1.现存量报表测试
2.审批，取消审批，删单测试
3.存货核算记账测试
4.本程序与原生程序交叉制单测试（即测试3张单，分别从用本程序，原生程序来测试同一料号）
5.新开料号与旧料号分别测试
6.新开仓库与旧仓库分别测试
7.领料申请和行关闭的制单测试
8.对应生产订单非审批状态下测试
********************/
USE [UFDATA_001_2024]
GO
/****** Object:  StoredProcedure [dbo].[SaveStockIn4MO]    Script Date: 2024-08-12 9:28:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SaveStockIn4MO]
@cCode1 int,@cMaker varchar(100),@cWhCode varchar(100),@MoDId int,@iQuantity decimal(30,10),
@loc varchar(100) = NULL,@doAudit int = 1,
@ufStr1 varchar(200) = NULL,@ufStr2 varchar(200)=NULL,@ufStr3 varchar(200)=NULL,@ufStr4 varchar(200)=NULL,@ufFloat1 varchar(200)=NULL,
@ufFloat2 varchar(200)=NULL,@ufInt1 int=NULL,@ufInt2 int=NULL,@ufDt1 datetime=NULL,@ufDt2 datetime=NULL,
 @msg varchar(500) output
--doAudit=是否审核,0=不审核，1=审核
AS
BEGIN

DECLARE @cCode VARCHAR(100)
SET @cCode = CONVERT(VARCHAR(100), @cCode1 + 1000000) 
SET @cCode = CONVERT(VARCHAR(6),GETDATE(),12) + @cCode  --加上年月日，比如240625 ，如果把VARCHAR(6)改成VARCHAR（4），则只取2406
SET @cCode = 'MD'+@cCode   --加上前缀
--此时@cCode应该是MD202425100012345 ,如果你的AUTOID = 12345的话

DECLARE @ACCID VARCHAR(100)
DECLARE @FATHERID VARCHAR(100)
DECLARE @CHILDID VARCHAR(100)
DECLARE @TODAY DATETIME
DECLARE @NOW DATETIME
DECLARE @COSTING BIT
DECLARE @cInvCode VARCHAR(100)
DECLARE @cDepCode VARCHAR(100)
DECLARE @iMQuantity decimal(30,10)
DECLARE @iNQuantity decimal(30,10)
DECLARE @cPsPcode VARCHAR(100)
DECLARE @cMoPode VARCHAR(100)
DECLARE @iMoSeq varchar(20)
DECLARE @MOID INT
DECLARE @iOpSeq varchar(20)
DECLARE @iPeSoDid INT
DECLARE @iPeSoType varchar(20)
DECLARE @iPeSoSeq INT
DECLARE @cPeSoCode varchar(100)
DECLARE @APPSTATUS INT
DECLARE @TMP INT
DECLARE @USELOC BIT
DECLARE @WRITE_ST_REAL_QTY INT = 1 --当1时，现存表时写实际量，0时写计划量
SET @ACCID = '001' --真实环境应当修改这个
SELECT @TODAY = CONVERT(DATETIME, CONVERT(CHAR(20), GETDATE(), 110))
SELECT @NOW = GETDATE()
SELECT @COSTING=bInCost,@USELOC=bWhPos FROM Warehouse WHERE @cWhCode = cWhCode ;
IF EXISTS(SELECT 1 FROM RdRecord10 WHERE cCode = @cCode)
BEGIN
SET @MSG = '单号重复'
RETURN 1 
END

IF (@USELOC = 1 AND NOT EXISTS (SELECT 1 FROM Position WHERE  cPosCode = @loc AND cWhCode = @cWhCode))
BEGIN
 SET @MSG = '仓库要求库位但没有提供'
 RETURN 10
END

IF (@USELOC = 0 AND  ISNULL(@loc,'') != '')
BEGIN
 SET @MSG = '仓库不要求库位但提供了库位'
 RETURN 11
END

SELECT @cInvCode = A.InvCode,@cDepCode=A.MDeptCode , 
@cMoPode = B.MoCode ,@iNQuantity=A.Qty,@iMoSeq = a.SortSeq ,
@iPeSoType = '7',@iPeSoSeq=a.sortseq,@iMQuantity = a.qty,@MOID=A.MOID,
@APPSTATUS=CASE 
	WHEN  ISNULL(QualifiedInQty,0)+@iQuantity > Qty THEN 1 --单据剩余可入量不足
	WHEN  Status != 3 THEN 2 --订单状态不是已审核
	END
FROM MOM_ORDERDETAIL A LEFT JOIN MOM_ORDER B ON A.MOID = B.MOID WHERE A.MoDid = @MoDId

IF (@cInvCode IS NULL)
BEGIN
SET @MSG = '不存在这个产品'
RETURN 2 
END 
IF (@APPSTATUS = 1)
BEGIN
SET @MSG = '单据剩余可入量不足'
RETURN 3 
END
IF (@APPSTATUS = 2)
BEGIN
SET @MSG = '订单状态不是已审核'
RETURN 4
END 
IF NOT EXISTS(SELECT 1 FROM Warehouse WHERE cWhCode = @cWhCode)
BEGIN
SET @MSG = '不存在该仓库'
RETURN 8 
END 

IF NOT EXISTS(SELECT 1 FROM  UFSystem.dbo.UA_Account WHERE cAcc_Id = @ACCID)
BEGIN
SET @MSG = 'ACCid配置错误'
RETURN 9 
END 
BEGIN TRANSACTION
--处理PK
SELECT @FATHERID=(1+iFatherId),@CHILDID=(1+iChildId) 
	FROM UFSystem.dbo.UA_Identity WITH(NOLOCK) WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
UPDATE UFSystem.dbo.UA_Identity SET iFatherId = @FATHERID,iChildId = @CHILDID WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
--写入材料出库单
INSERT INTO RdRecord10(ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,cMaker,
	cHandler,dVeriDate,VT_ID,dnmaketime,dnverifytime,cDepCode,cMPoCode ,iproorderid,
	cDefine11,cDefine12,cDefine13,cDefine14,cDefine7,cDefine16,cDefine5,cDefine15,cDefine4,cDefine6)
VALUES(@FATHERID+1000000000,1,'10','成品入库','生产订单',@cWhCode,@TODAY,@cCode,@cMaker,
	CASE @doAudit WHEN 0 THEN NULL ELSE @cMaker END,
	CASE @doAudit WHEN 0 THEN NULL ELSE @TODAY END,
	63,@NOW,CASE @doAudit WHEN 0 THEN NULL ELSE @NOW END,@cDepCode,@cMoPode,@MOID,
	@ufStr1,@ufStr2,@ufStr3,@ufStr4,@ufFloat1,@ufFloat2,@ufInt1,@ufInt2,@ufDt1,@ufDt2);
IF @@ROWCOUNT != 1
BEGIN
	SET @MSG = 'SQL执行INSERT INTO RdRecord12错误'
	ROLLBACK TRANSACTION
	RETURN 101
END
--写入入库单明细
INSERT INTO RdRecords10(AutoID,ID,cInvCode,iQuantity,irowno,bCosting,iNQuantity,iMPoIds,cmocode,iorderdid,isotype,brelated,imoseq,iposflag,cPosition) 
VALUES(@CHILDID+1000000000,@FATHERID+1000000000,@cInvCode,@iQuantity,1,@COSTING,@iNQuantity,@modid,@cMoPode,0,0,0,@iMoSeq,@USELOC,@loc);
IF @@ROWCOUNT != 1
BEGIN
	SET @MSG = 'SQL执行INSERT INTO RdRecords10错误'
	ROLLBACK TRANSACTION
	RETURN 102
END
INSERT INTO IA_ST_UnAccountVouch10(IDUN,IDSUN,cVouTypeUN,cBustypeUN) VALUES(@FATHERID+1000000000,@CHILDID+1000000000,10,'成品入库');
--更新库位信息
IF(@USELOC=1)
BEGIN
	INSERT INTO InvPosition (RdsID,RdID,cWhCode,cPosCode,cInvCode,iQuantity,cHandler,dDate,bRdFlag,iTrackId,cvouchtype,dVouchDate)
	  VALUES(@CHILDID+1000000000,@FATHERID+1000000000,@cWhCode,@loc,@cInvCode,@iQuantity,@cMaker,@TODAY,1,0,'10',@TODAY);
	IF @@ROWCOUNT != 1
	BEGIN
		SET @MSG = 'SQL执行INSERT INTO InvPosition错误'
		ROLLBACK TRANSACTION
		RETURN 106
	END
	IF(NOT EXISTS (SELECT 1 FROM InvPositionSum WHERE cWhCode=@cWhCode AND cPosCode = @loc AND cInvCode = @cInvCode))
	BEGIN
		INSERT INTO InvPositionSum (cWhCode,cPosCode,cInvCode,iQuantity) VALUES(@cWhCode,@loc,@cInvCode,@iQuantity);
	END
	ELSE
	BEGIN
	    UPDATE InvPositionSum SET iQuantity = @iQuantity+ISNULL(iQuantity,0)  WHERE cWhCode=@cWhCode AND cPosCode = @loc AND cInvCode = @cInvCode; 
	END
	IF @@ROWCOUNT != 1
	BEGIN
		SET @MSG = 'SQL执行INSERT INTO InvPositionSum错误'
		ROLLBACK TRANSACTION
		RETURN 107
	END
END


--更新仓库现存量，如果需要支持其他维度如批次维度等，需要在这里进行评估
IF (@doAudit=0 AND EXISTS(SELECT 1 from accinformation WHERE csysid = 'ST' and cname = 'bProductInCheck' AND  lower(CVALUE)='true' ))
BEGIN
	SET @WRITE_ST_REAL_QTY = 0
END
ELSE
BEGIN
	SET @WRITE_ST_REAL_QTY = 1
END


IF EXISTS(SELECT 1 FROM CurrentStock WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode)
BEGIN
	UPDATE CurrentStock set 
		iQuantity = iQuantity + CASE @WRITE_ST_REAL_QTY WHEN 1 THEN @iQuantity ELSE 0 END,
		fInQuantity = fInQuantity +  CASE @WRITE_ST_REAL_QTY WHEN 0 THEN @iQuantity ELSE 0 END 
	WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode
	IF @@ROWCOUNT != 1
	BEGIN
	SET @MSG = 'SQL执行UPDATE CurrentStock错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
	ROLLBACK TRANSACTION
	RETURN 103
	END
END
ELSE 
BEGIN
	IF NOT EXISTS(SELECT 1 FROM SCM_Item WHERE cInvCode = @cInvCode )
	BEGIN
		INSERT INTO SCM_Item(cInvCode,PartId) VALUES(@cInvCode,0);
		SET @TMP = @@IDENTITY
	END
	ELSE
	BEGIN
	    SELECT @TMP = ID FROM SCM_ITEM WHERE cInvCode = @cInvCode
	END

    INSERT INTO CurrentStock(cWhCode,cInvCode,ItemId,iQuantity,fInQuantity,iSodid)
	VALUES(@cWhCode,@cInvCode,@TMP ,
		 CASE @WRITE_ST_REAL_QTY WHEN 1 THEN @iQuantity ELSE 0 END,
		 CASE @WRITE_ST_REAL_QTY WHEN 0 THEN @iQuantity ELSE 0 END,'');
	IF @@ROWCOUNT != 1
	BEGIN
	SET @MSG = 'SQL执行INSERT CurrentStock错误'
	ROLLBACK TRANSACTION
	RETURN 104
	END
END

--回写对应的生产订单发料记录
UPDATE mom_orderdetail  SET QualifiedInQty = ISNULL(QualifiedInQty,0) + @iQuantity WHERE modid = @modid
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行UPDATE mom_modetail错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 105
END
SET @msg = @cCode
COMMIT TRANSACTION
END